﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using EZCRS.HCMS.Data;
using EZCRS.HCMS.Entity;


namespace EZCRS.HCMS.SqlClient
{
    public class sql_AdmHotel
    {
        string SqlStr = "";
        #region 酒店的删除
        /// <summary>
        /// 酒店的删除
        /// </summary>
        /// <param name="Htlid"></param>
        /// <returns></returns>
        public bool Hotel_Del(string pidlist)
        {
            #region 独立
            //string sql = "", msg = "";
            //sql = "delete from hfg_hotel where htlid=" + Htlid + "";
            //bool result = CommonDataAccess.Delete(sql, out msg);
            //return result;
            #endregion

            #region 集成 hubs1
            string sql = "";
            sql = "delete from hotel where pid in (" + pidlist + ")";
            if (ExecSql.SqlExecNoquery(sql) > 0)
                return true;
            else
                return false;
            #endregion
        }
        #endregion

        #region 加入集团或从集团中移除
        /// <summary>
        /// 加入集团
        /// </summary>
        /// <param name="pidlist"></param>
        /// <param name="gid"></param>
        /// <returns></returns>
        public bool addintogroup(string pidlist, string gid)
        {
            SqlStr = "update hotel set groupid=" + gid + " where pid in (" + pidlist + ")";
            if (ExecSql.SqlExecNoquery(SqlStr) > 0)
                return true;
            else
                return false;
        }
        /// <summary>
        /// 从集团中移除
        /// </summary>
        /// <param name="pidlist"></param>
        /// <param name="gid"></param>
        /// <returns></returns>
        public bool removefromgroup(string pidlist, string gid)
        {
            SqlStr = "update hotel set groupid=13 where pid in (" + pidlist + ")";
            if (ExecSql.SqlExecNoquery(SqlStr) > 0)
                return true;
            else
                return false;
        }
        #endregion

        #region 酒店Info数据的显示,显示一条数据
        /// <summary>
        /// 酒店Info数据的显示,显示一条数据
        /// </summary>
        /// <param name="HtlId"></param>
        /// <returns></returns>
        public DataRow Hotel_Item(string HtlId)
        {
            #region 独立
            //try
            //{
            //    return ExecSql.GetDataSet("select * from hfg_hotel where htlid=" + HtlId).Tables[0].Rows[0];
            //}
            //catch
            //{
            //    return null;
            //}
            #endregion

            #region 集成 hubs1
            try
            {
                return ExecSql.GetDataSet("select * from hotel where hotel_id=" + HtlId).Tables[0].Rows[0];
            }
            catch
            {
                return null;
            }
            #endregion

        }
        #endregion

        public bool AddHotel(string Pid, string gid, string htldomain, string inuser, string Htlid)
        {
            SqlStr = "select * from hotel where pid=" + Pid + "";
            DataTable dt = ExecSql.GetDataSet(SqlStr).Tables[0];
            if (dt.Rows.Count > 0)
            {
                if (Htlid != "")
                    SqlStr = "update hfg_hotel set htldomain=@htldomain,gid=@gid,indate='" + DateTime.Now.ToString() + "',inuser=@inuser where htlid=" + Htlid + "";
                else
                {
                    SqlStr = "insert into hfg_hotel(htldomain,htlname,pid,pid_e,gid,inuser,indate)";
                    SqlStr += "                 values(@htldomain,@htlname,@pid,@pid_e,@gid,@inuser,'" + DateTime.Now.ToString() + "')";
                }
                SqlParameter[] pam = new SqlParameter[6];
                pam[0] = new SqlParameter("@htldomain", htldomain);
                pam[1] = new SqlParameter("@htlname", dt.Rows[0]["title"].ToString());
                pam[2] = new SqlParameter("@pid", Pid);
                pam[3] = new SqlParameter("@pid_e", dt.Rows[0]["pid_e"].ToString());
                pam[4] = new SqlParameter("@gid", gid);
                pam[5] = new SqlParameter("@inuser", inuser);
                if (ExecSql.SqlExecNoquery(SqlStr, pam) > 0)
                    return true;
                else
                    return false;
            }
            else
                return false;
        }

        /// <summary>
        /// 返回酒店名称和pid
        /// </summary>
        /// <param name="PIdList">酒店列表；逗号分割的酒店编号</param>
        /// <returns></returns>
        public DataTable HotelName_List(string PIdList)
        {
            #region 独立
            //return ExecSql.GetDataSet("select htlname as title,htlid from hfg_hotel where htlid in (" + PIdList + ") order by htlid").Tables[0];
            #endregion

            #region 集成 hubs1
            return ExecSql.GetDataSet("select title,hotel_id as htlid,pid from hcrs_hotel where hotel_id in (" + PIdList + ") order by hotel_id").Tables[0];
            #endregion
        }

        /// <summary>
        /// 返回酒店名和pid
        /// </summary>
        /// <param name="GroupId">集团id 没有为空</param>
        /// <param name="RType">类型 1=所有酒店 2=集团酒店 3=所有非集团酒店</param>
        /// <returns></returns>
        public DataTable HotelName_List(string GroupId, int RType)
        {
            #region 独立

            //if (RType == 2)
            //{
            //    return CommonDataAccess.GetTable("select htlname as title,htlid from hfg_hotel where gid=" + CFun.VerifySQL(GroupId) + " order by pid", "tab");
            //}
            //else if(RType==1)
            //    return CommonDataAccess.GetTable("select htlname as title,htlid from hfg_hotel order by pid ", "tab");
            //else
            //    return CommonDataAccess.GetTable("select htlname as title,htlid from hfg_hotel where gid=0 order by pid ", "tab");
            #endregion

            #region 集成 hubs1
            if (RType == 2)
            {
                return ExecSql.GetDataSet("select title,hotel_id as htlid,pid from hcrs_hotel where groupid=" + GroupId + " order by hotel_id").Tables[0];
            }
            else
                return ExecSql.GetDataSet("select title,hotel_id as htlid,pid from hcrs_hotel order by hotel_id ").Tables[0];

            #endregion

        }
        #region 酒店列表
        public DataTable Hotel_List(string StrWhere)
        {
            #region 独立
            //StringBuilder strSql = new StringBuilder();
            //strSql.Append("select * from hfg_hotel where 1=1 ");
            //if (StrWhere.Trim() != "")
            //    strSql.Append(StrWhere);
            //return CommonDataAccess.GetTable(strSql.ToString(), "dt");
            #endregion

            #region 集成hubs1
            string strSql = "select * from hotel where 1=1 ";
            if (StrWhere.Trim() != "")
                strSql += StrWhere;
            return ExecSql.GetDataSet(strSql).Tables[0];
            #endregion
        }

        public DataSet Hotel_List(int cpage, string hotelname, string hoteladdress, string pid, string groupid)
        {
            if (groupid == "0")
                SqlStr = " groupid=13 ";
            else
                SqlStr = " groupid=" + groupid + " ";

            if (hotelname != "")
                SqlStr += " and title like '%" + hotelname + "%' ";
            if (hoteladdress != "")
                SqlStr += " and address1 like '%" + hoteladdress + "%'";
            if (pid != "")
                SqlStr += " and pid=" + pid + " ";

            return ExecSql.AddParamFrKeyField("hotel", " hotel_id,title,address1,contact_name,phone,pid ", "hotel_id desc", "hotel_id", CFun.PageSize, cpage, SqlStr);
        }

        public DataSet Hotel_List(int cpage, string hotelname, string hoteladdress, string pid)
        {
            SqlStr = " (groupid=13 or groupid=7) ";

            if (hotelname != "")
                SqlStr += " and title like '%" + hotelname + "%' ";
            if (hoteladdress != "")
                SqlStr += " and address1 like '%" + hoteladdress + "%'";
            if (pid != "")
                SqlStr += " and pid=" + pid + " ";

            return ExecSql.AddParamFrKeyField("hotel", " hotel_id,title,address1,contact_name,phone,pid ", "hotel_id desc", "hotel_id", CFun.PageSize, cpage, SqlStr);
        }
        #endregion
        /// <summary>
        /// 返回酒店名称和pid
        /// </summary>
        /// <param name="PIdList">酒店列表；逗号分割的酒店编号</param>
        /// <returns></returns>
        public DataTable Hotel_Title_List(string Title)
        {
            #region 独立
            //return CommonDataAccess.GetTable("select title,pid from hotel where title like '%" + CFun.VerifySQL(Title) + "%'", "tab");
            #endregion

            #region 集成 hubs1
            return ExecSql.GetDataSet("select title,pid from hcrs_hotel where title like '%" + Title + "%'").Tables[0];
            #endregion
        }
        public DataTable Hotel_Title_List(int pid)
        {
            return ExecSql.GetDataSet("select title,pid from hcrs_hotel where pid=" + pid.ToString() + "").Tables[0];
        }

        #region 酒店照片

        /// <summary>
        /// 根据酒店ID获取照片 Jay
        /// </summary>
        /// <param name="HotelCode">酒店ID</param>
        /// <returns></returns>
        public DataTable GetHotelPhoto(int HotelCode)
        {
            return ExecSql.GetDataSet("SELECT photoid, photoname_e, photoname, photourl, phototype FROM photo WHERE userid=" + HotelCode.ToString() + " ORDER BY photoid DESC").Tables[0];
        }

        /// <summary>
        /// 根据照片ID获取照片
        /// </summary>
        /// <param name="PhotoID">照片ID</param>
        /// <returns></returns>
        public DataTable GetHotelPhotoByID(int PhotoID)
        {
            return ExecSql.GetDataSet("SELECT photoid, photoname_e, photoname, photourl, phototype FROM photo WHERE photoid=" + PhotoID.ToString()).Tables[0];
        }

        /// <summary>
        /// 添加照片
        /// </summary>
        /// <param name="HotelCode">酒店ID</param>
        /// <param name="PhotoName">照片名称</param>
        /// <param name="Url">照片路径</param>
        /// <param name="PhotoType">照片类型</param>
        /// <param name="ID">返回最新ID</param>
        /// <returns></returns>
        public bool AddPhoto(int HotelCode, string PhotoName, string Url, int PhotoType, out int ID)
        {

            string strSQL = "INSERT INTO photo(photoname, photourl, phototype, userid) VALUES(@v_PhotoName, @v_Url, @i_Type, @i_HotelID);SET @i_ID = @@IDENTITY";

            SqlParameter[] param = new SqlParameter[5];

            param[0] = new SqlParameter("@i_HotelID", HotelCode);
            param[1] = new SqlParameter("@v_PhotoName", PhotoName);
            param[2] = new SqlParameter("@v_Url", Url);
            param[3] = new SqlParameter("@i_Type", PhotoType);

            param[4] = new SqlParameter("@i_ID", SqlDbType.Int);
            param[4].Direction = ParameterDirection.Output;

            //param[4] = new SqlParameter("@i_HotelID", HotelCode);
            //param[5] = new SqlParameter("@i_HotelID", HotelCode);


            bool b = (ExecSql.SqlExecNoquery(strSQL, param) > 0) ? true : false;

            ID = Convert.ToInt32(param[4].Value);

            return b;
        }

        /// <summary>
        /// 更新照片
        /// </summary>
        /// <param name="PhotoID">照片ID</param>
        /// <param name="PhotoName">照片名称</param>
        /// <param name="Url">URL</param>
        /// <param name="PhotoType">照片类型</param>
        /// <returns></returns>
        public bool UpdatePhoto(int PhotoID, string PhotoName, string Url, int PhotoType)
        {
            string strSQL = "UPDATE photo SET photoname = @v_PhotoName, photourl = @v_Url, phototype = @i_Type WHERE photoid = @i_ID";

            SqlParameter[] param = new SqlParameter[4];

            param[0] = new SqlParameter("@i_ID", PhotoID);
            param[1] = new SqlParameter("@v_PhotoName", PhotoName);
            param[2] = new SqlParameter("@v_Url", Url);
            param[3] = new SqlParameter("@i_Type", PhotoType);

            //param[4] = new SqlParameter("@i_HotelID", HotelCode);
            //param[5] = new SqlParameter("@i_HotelID", HotelCode);

            return (ExecSql.SqlExecNoquery(strSQL, param) > 0) ? true : false;

        }


        /// <summary>
        /// 更新照片信息
        /// </summary>
        /// <param name="PhotoID">照片ID</param>
        /// <param name="PhotoName">照片名称</param>
        /// <param name="PhotoType">照片类型</param>
        /// <returns></returns>
        public bool UpdatePhoto(int PhotoID, string PhotoName, int PhotoType)
        {
            string strSQL = "UPDATE photo SET photoname = @v_PhotoName, phototype = @i_Type WHERE photoid = @i_ID";

            SqlParameter[] param = new SqlParameter[3];

            param[0] = new SqlParameter("@i_ID", PhotoID);
            param[1] = new SqlParameter("@v_PhotoName", PhotoName);
            param[2] = new SqlParameter("@i_Type", PhotoType);

            //param[4] = new SqlParameter("@i_HotelID", HotelCode);
            //param[5] = new SqlParameter("@i_HotelID", HotelCode);

            return (ExecSql.SqlExecNoquery(strSQL, param) > 0) ? true : false;

        }

        /// <summary>
        /// 删除照片记录
        /// </summary>
        /// <param name="PhotoID"></param>
        /// <returns></returns>
        public bool DeletePhoto(int PhotoID)
        {
            string strSQL = "DELETE FROM photo WHERE photoid = @i_ID";

            SqlParameter[] param = new SqlParameter[1];

            param[0] = new SqlParameter("@i_ID", PhotoID);

            //param[4] = new SqlParameter("@i_HotelID", HotelCode);
            //param[5] = new SqlParameter("@i_HotelID", HotelCode);

            return (ExecSql.SqlExecNoquery(strSQL, param) > 0) ? true : false;

        }

        #endregion

        #region 酒店价格代码

        /// <summary>
        /// 获取酒店价格代码
        /// </summary>
        /// <param name="Pid">酒店ID</param>
        /// <param name="State">激活</param>
        /// <param name="Page">页码</param>
        /// <returns></returns>
        public DataSet GetHotelRateList(int Pid, int State, int Page)
        {
            if (Page <= 0) Page = 1;

            string sqlWhere = " property_id = " + Pid.ToString();

            sqlWhere += " AND status = " + State.ToString();

            return ExecSql.AddParamFrKeyField("HCRS_RateCode", "id, rate, [name], desc1, GuaranteeRule, CancelRule, Status, currency, basis", "id desc", "id", CFun.PageSize, Page, sqlWhere);
        }

        public DataSet GetHotelRateList(int Pid, int State, int Page, int PageSize)
        {
            if (Page <= 0) Page = 1;

            string sqlWhere = " property_id = " + Pid.ToString();

            sqlWhere += " AND status = " + State.ToString();

            return ExecSql.AddParamFrKeyField("HCRS_RateCode", "id, rate, [name], desc1, GuaranteeRule, CancelRule, Status, currency, basis", "id desc", "id", PageSize, Page, sqlWhere);
        }

        #endregion
    }
}
